package com.vogue.supplymall.order.domain.provider;

import java.util.Date;

import com.vogue.ssm.database.provider.BaseSqlProvider;

public class OrderPullSqlProvider extends BaseSqlProvider{

	/**
	 * 拉取卖家订单的语句
	 * 
	 * @param syscodeid	卖家系统唯一号id
	 * @return
	 */
	public String pullSell(Long syscodeid, Date maxdate) {
		StringBuffer buf = new StringBuffer();
		buf.append("select oip.pname productname,")//商品名称
		.append("o.tendid tendid,") //所属平台id
		.append("o.id id,") //订单id
		.append("ifnull(opv.paymentid,'') paymentid,") //支付单id
		.append("ifnull(opv.url,'') payurl,") //支付凭证路径
		.append("oip.punit productunit,")//商品单位
		.append("oi.itemquantity productquantity,")//商品数量
		.append("oip.price productprice,")//商品单价
		.append("0 discountprice,")//优惠金额
		.append("oi.id supplyid,")//订单项目基本信息id
		.append("oip.pmatchid itemid,")//关联的品项id
		.append("oip.umatchid unitid,")//关联的品项单位id
		.append("oi.matchid buystoreid,")//买家仓库id
		.append("oi.`storage` buystorename,")//买家仓库名称
		.append("oi.sendcount sendcount,")//发货数量
		.append("oi.sendmoney sendmoney,")//发货金额
		.append("oi.diffcount diffcount,")//差异数量
		.append("oi.diffmoney diffmoney,")//差异金额
		.append("o.ordernumber,")//平台单号
		.append("date_format(o.createdate, '%Y-%m-%d %H:%i:%S') createdate,")//平台日期
		.append("ob.entname buyentname,")//平台客户集团名称
		.append("ob.storename buyshopname,")//平台客户门店名称
		.append("CONCAT(ob.syscodeid,'') buyentid,")//平台客户集团id
		.append("CONCAT(ob.organid,'') organid,")//平台客户门店id
		.append("ob.matchid buyshopid,")//平台客户门店matchid
		.append("ob.buyeraddress buyaddress,")//买家地址
		.append("ob.buyername buyname,")//买家姓名
		.append("ob.btel buytel,")//买家电话
		.append("ob.syscode,")//平台客户开通号码
		.append("o.memo,")//订单备注
		.append("o.freight,")//运费
		.append("o.finalprice,")//实付款金额
		.append("o.totalprice,")//商品总金额
		.append("os.matchid sellshopid, ")//卖家销售商品店铺id
		.append("ifnull(os.shopname,'') sellshopname, ")//卖家销售商品店铺名称
		.append("CONCAT(os.syscodeid,'') sellentid, ")//卖家销售商品企业id
		.append("ifnull(os.entname,'') sellentname ")//卖家销售商品企业名称
		.append("from `order` o")//订单信息表
		.append(" join order_seller os on o.id = os.orderid")//订单卖家信息
		.append(" join order_buyer ob on o.id=ob.orderid")//订单买家信息表
		.append(" join order_item_product oip on oip.orderid=o.id")//订单项目的商品信息表
		.append(" join order_item oi on oi.id = oip.orderitemid")//订单项目基本信息表
		.append(" join order_status ost on ost.orderid = o.id")//订单状态信息表
		.append(" left join order_pay_voucher opv on opv.orderid=o.id")//支付凭证信息表（线下）
		.append(" where ost.iscancel=0 and ost.orderstatus=2 and ost.pullstate<>1")//待发货并且是卖家未拉取过的
		.append(" and not exists (select 1 from order_cancel oc where oc.orderid = o.id and oc.cancelstate = 1)")//没有申请取消的
		.append(" and os.syscodeid = #{arg0}")
		.append(" and timestampdiff(SECOND,o.createdate,#{arg1}) > 2 ")
		.append(" order by o.ordernumber,oip.pmatchid,opv.url");
		return buf.toString();
	}

	/**
	 * 获取卖家拉取订单总数量
	 * @param syscodeid	卖家注册号id
	 * @param maxdate	拉取订单的最大创建时间
	 * @return
	 */
	public String pullSellCount(Long syscodeid, Date maxdate) {
		StringBuffer buf = new StringBuffer();
		buf.append("select count(o.id) totalCnt ")//商品名称
		.append(" from `order` o,")//订单信息表
		.append(" order_status ost,")//订单状态信息表
		.append(" order_seller os ")//订单卖家信息
		.append(" where ost.iscancel=0 and ost.orderstatus=2 and ost.pullstate<>1")//待发货并且是卖家未拉取过的
		.append(" and not exists (select 1 from order_cancel oc where oc.orderid = o.id and oc.cancelstate = 1)")//没有申请取消的
		.append(" and ost.orderid = o.id and o.id = os.orderid")
		.append(" and os.syscodeid = #{arg0}")
		.append(" and timestampdiff(SECOND,o.createdate,#{arg1}) > 2 ");
		return buf.toString();
	}
	
	/**
	 * 拉取买家订单的语句
	 * 
	 * @param syscodeid	买家系统唯一号id
	 * @return
	 */
	public String pullBuy(Long syscodeid, Date maxDate) {
		StringBuffer buf = new StringBuffer();
		buf.append("select distinct oip.pname productname,")//商品名称
		.append("o.tendid tendid,") //所属平台id
		.append("o.id id,") //订单id 
		.append("ifnull(opv.paymentid,'') paymentid,") //支付单id
		.append("ifnull(opv.url,'') payurl,") //支付凭证路径
		.append("oip.punit productunit,")//商品单位
		.append("oi.itemquantity productquantity,")//商品数量
		.append("oip.price productprice,")//商品单价
		.append("0 discountprice,")//优惠金额
		.append("oi.id supplyid,")//订单项目基本信息id
		.append("oip.pmatchid itemid,")//关联的品项id
		.append("oip.umatchid unitid,")//关联的品项单位id
		.append("oi.matchid buystoreid,")//买家仓库id
		.append("oi.`storage` buystorename,")//买家仓库名称
		.append("oi.sendcount sendcount,")//发货数量
		.append("oi.sendmoney sendmoney,")//发货金额
		.append("oi.diffcount diffcount,")//差异数量
		.append("oi.diffmoney diffmoney,")//差异金额
		.append("o.ordernumber,")//平台单号
		.append("date_format(o.createdate, '%Y-%m-%d %H:%i:%S') createdate,")//平台日期
		.append("ob.entname buyentname,")//平台客户集团名称
		.append("ob.storename buyshopname,")//平台客户门店名称
		.append("CONCAT(ob.syscodeid,'') buyentid,")//平台客户集团id
		.append("CONCAT(ob.organid,'') organid,")//平台客户门店id
		.append("ob.matchid buyshopid,")//平台客户门店id
		.append("ob.buyeraddress buyaddress,")//买家地址
		.append("ob.buyername buyname,")//买家姓名
		.append("os.stel buytel,")//买家电话
		.append("ob.syscode,")//平台客户开通号码
		.append("o.memo,")//订单备注
		.append("o.freight,")//运费
		.append("o.finalprice,")//实付款金额
		.append("o.totalprice,")//商品总金额
		.append("os.matchid sellshopid, ")//卖家销售商品店铺id
		.append("os.shopname sellshopname, ")//卖家销售商品店铺名称
		.append("CONCAT(os.syscodeid,'') sellentid, ")//卖家销售商品企业id
		.append("os.entname sellentname ")//卖家销售商品企业名称
		.append("from `order` o")//订单信息表
		.append(" join order_buyer ob on o.id=ob.orderid")//订单买家信息表
		.append(" join order_seller os on o.id = os.orderid")//订单卖家信息
		.append(" join order_item_product oip on oip.orderid=o.id")//订单项目的商品信息表
		.append(" join order_item oi on oi.id = oip.orderitemid")//订单项目基本信息表	
		.append(" join order_status ost on ost.orderid = o.id")//订单状态信息表		
		.append(" left join order_pay_voucher opv on opv.orderid=o.id")//支付凭证信息表（线下）
		.append(" where ost.iscancel=0 and ost.orderstatus=3 and ost.buypullstate<>1")//已发货并且是买家未拉取过的
		.append(" and ob.syscodeid = #{arg0}")
		.append(" and ob.buyertype = 1")	//买家只能拉取企业下的订单
		.append(" and timestampdiff(SECOND,o.createdate,#{arg1}) > 2 ")
		.append(" order by o.ordernumber,oip.pmatchid,opv.url");
		return buf.toString();
	}
	
	/**
	 * 获取买家拉取订单总数量
	 * @param syscodeid	卖家注册号id
	 * @param maxDate	拉取订单的最大创建时间
	 * @return
	 */
	public String pullBuyCount(Long syscodeid, Date maxDate) {
		StringBuffer buf = new StringBuffer();
		buf.append("select count(o.id) totalCnt ")//商品名称
		.append(" from `order` o,")//订单信息表
		.append(" order_status ost,")//订单状态信息表
		.append(" order_buyer ob ")//订单卖家信息
		.append("where ost.iscancel=0 and ost.orderstatus=3 and ost.buypullstate<>1")//已发货并且是买家未拉取过的
		.append(" and o.id = ob.orderid and ost.orderid = o.id")
		.append(" and ob.syscodeid = #{arg0}")
		.append(" and timestampdiff(SECOND,o.createdate,#{arg1}) > 2 ");
		return buf.toString();
	}

	/**
	 * 获取卖家拉取退货订单总数量
	 * @param syscodeid	卖家注册号id
	 * @param maxdate	拉取订单的最大创建时间
	 * @return
	 */
	public String pullRefundSellCount(Long syscodeid, Date maxdate) {
		StringBuffer buf = new StringBuffer();
		buf.append("select count(r.id) totalCnt ")
				.append(" from refund_order r,")//退后订单信息表
				.append(" refund_status ost,")//退货订单状态信息表
				.append("`order` o,")//订单信息表
				.append(" order_status s,")//订单状态信息表
				.append(" order_seller os ")//订单卖家信息
				.append(" where ost.refundstatus = 0 and ost.sellerpullstate<>1 ")//卖家同意并且是卖家未拉取过的
				.append(" and ost.refundid = r.id and os.orderid = r.orderid and r.disabledflag = 0 ")
				.append(" AND r.autoflag = 0 ")//自动退货单不需要拉取
				.append(" and o.id = os.orderid and r.orderid = o.id ")
				.append(" and s.orderid = o.id and s.orderstatus = 4 and s.iscancel =0 and s.pullstate = 1 ")//相关订单被拉取过才可以拉取退货单
				.append(" and os.syscodeid = #{arg0}")
				.append(" and timestampdiff(SECOND,r.createdate,#{arg1}) > 2 ");
		return buf.toString();
	}

	/**
	 * 拉取卖家退后订单的语句
	 * @param syscodeid	卖家系统唯一号id
	 * @return
	 */
	public String pullRefundSell(Long syscodeid, Date maxdate) {
		StringBuffer buf = new StringBuffer();
		buf.append("select oip.pname productname,")//商品名称
				.append("r.tendid,") //所属平台id
				.append("r.id,") //订单id
				.append("oip.punit productunit,")//商品单位
				.append("oi.itemquantity productquantity,")//商品数量
				.append("d.refundquantity,")//商品退货数量
				.append("d.refundprice productprice,")//商品单价
				.append("0 discountprice,")//优惠金额
				.append("d.refundprice*d.refundquantity refundmoney,")//退货金额
				.append("oi.id supplyid,")//平台单号明细id
				.append("d.id refunddetailid,")//退单明细id
				.append("oip.pmatchid itemid,")//关联的品项id
				.append("oip.umatchid unitid,")//关联的品项单位id
				.append("oi.matchid buystoreid,")//买家仓库id
				.append("oi.`storage` buystorename,")//买家仓库名称
				.append("r.refundnumber,")//平台退单单号
				.append("o.ordernumber,")//平台订单单号
				.append("date_format(r.createdate, '%Y-%m-%d %H:%i:%S') createdate,")//平台日期
				.append("ob.entname buyentname,")//平台客户集团名称
				.append("ob.storename buyshopname,")//平台客户门店名称
				.append("CONCAT(ob.syscodeid,'') buyentid,")//平台客户集团id
				.append("CONCAT(ob.organid,'') organid,")//平台客户门店id
				.append("ob.matchid buyshopid,")//平台客户门店matchid
				.append("ob.buyeraddress buyaddress,")//买家地址
				.append("ob.buyername buyname,")//买家姓名
				.append("ob.btel buytel,")//买家电话
				.append("ob.syscode,")//平台客户开通号码
				.append("r.memo,")//订单备注
				.append("r.cause refundreason,")//订单退货原因
				.append("o.totalprice,")//订单商品总金额
				.append("r.freight refundfreight,")//退货单运费
				.append("r.finalprice-r.freight refundtotalprice,")//退货单订单总金额
				.append("r.finalprice refundfinalprice,")//退货单实付款金额=退货单运费+退货单订单总金额
				.append("os.matchid sellshopid, ")//卖家销售商品店铺id
				.append("ifnull(os.shopname,'') sellshopname, ")//卖家销售商品店铺名称
				.append("CONCAT(os.syscodeid,'') sellentid, ")//卖家销售商品企业id
				.append("ifnull(os.entname,'') sellentname, ")//卖家销售商品企业名称
				.append("case WHEN u.type=0 then '0' else '1' END imgtype,")//[0:图片 1:视频],
				.append("u.url,")//资源路径
				.append("u.id refundimgId ")//资源路径
				.append("from order_item_product oip,")//订单项目的商品信息表
				.append("order_item oi,")//订单项目基本信息表
				.append("`order` o,")//订单信息表
				.append("order_status s,")//订单状态信息表
				.append("order_seller os,")//订单卖家信息
				.append("order_buyer ob,")//订单买家信息表
				.append("refund_status ost, ")//退货订单状态信息表
				.append("refund_order r,")//退货订单信息
				.append("refund_detail d ")//退货订单明细信息
				.append(" LEFT join refund_upload u on u.refunddetailid = d.id ")//退后明细
				.append(" where ost.refundstatus = 0 and ost.sellerpullstate<>1 ")//卖家同意并且是卖家未拉取过的
				.append(" AND ost.refundid = r.id ")
				.append(" AND r.autoflag = 0 ")//自动退货单不需要拉取
				.append(" and oi.id = oip.orderitemid ")
				.append(" and oip.orderid=o.id and o.id=ob.orderid and o.id = os.orderid")
				.append(" and r.orderid = o.id and r.id = d.refundid ")
				.append(" and d.oiid=oi.id and d.oipid=oip.id ")
				.append(" and s.orderid = o.id and s.orderstatus = 4 and s.iscancel =0 and s.pullstate = 1 ")//相关订单被拉取过才可以
				.append(" and os.syscodeid = #{arg0}")
				.append(" and timestampdiff(SECOND,r.createdate,#{arg1}) > 2 ")
				.append(" order by r.refundnumber ");
		return buf.toString();
	}

}
